package vip.xiaonuo.smzq.modular.dictionary.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.dictionary.entity.Cb08Xzqh;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

/**
 * 行政区划 Mapper
 *
 * @author eomer
 * @since 2024-02-21
 */
@Mapper
public interface Cb08XzqhMapper extends BaseMapper<Cb08Xzqh> {
    @Select("select dm from kcl_cb08_xzqh where fdm =#{sql} ")
    List<Integer> selectXzqhDm(Integer sql);

    /**
     * 获取行政区划名称 例：河北省 > 邯郸市 > 丛台区
     * @param xzqhdm 县行政区划编码
     * @return 行政区划名称
     */
    @Select("SELECT COALESCE(s.mc, '') || ' > ' || COALESCE(c.mc, '') || ' > ' || x.mc AS xzqhmc FROM kcl_cb08_xzqh x \n" +
            "LEFT JOIN kcl_cb08_xzqh c ON x.fdm = c.dm\n" +
            "LEFT JOIN kcl_cb08_xzqh s ON c.fdm = s.dm\n" +
            "WHERE x.dm =#{xzqhdm}")
    String getXzqhmc(int xzqhdm);

    /**
     * 默认查询所有行政区划
     * @param fistXzq
     * @return
     */
    @Select("select dm from kcl_cb08_xzqh where dm::varchar like  #{fistXzq} || '%'")
    List<String> selXzqhDm(String fistXzq);

    /**
     * 根据条件搜索行政区代码
     * @param fistXzq 用户身份行政区
     * @param xzqdm 搜索行政区
     * @return
     */
    @Select(" with \n" +
            "  allxzq AS(select x.dm from kcl_cb08_xzqh x \n" +
            "\tleft join kcl_cb08_xzqh shi on x.fdm=shi.dm \n" +
            "\tleft JOIN kcl_cb08_xzqh sheng on shi.fdm=sheng.dm \n" +
            "\twhere x.dm::varchar like  #{fistXzq} || '%'\n" +
            "\tORDER BY x.dm\n" +
            "\t)\n" +
            "\t\n" +
            "\tselect * from allxzq where dm::varchar like #{xzqdm} || '%'")
    List<String> getXzqhdm(@Param("fistXzq") String fistXzq,@Param("xzqdm") String xzqdm);

    /**
     * 根据矿区编号条件搜索行政区代码
     * @param fistXzq 用户身份行政区
     * @param kqbh 矿区编号
     * @return
     */
    @Select("select x.dm from kcl_ks k \n" +
            "  left join kcl_cb08_xzqh x   on k.xzqdm =x.dm\n" +
            "\tleft join kcl_cb08_xzqh shi on x.fdm=shi.dm \n" +
            "\tleft JOIN kcl_cb08_xzqh sheng on shi.fdm=sheng.dm \n" +
            "\twhere x.dm::varchar like #{fistXzq} || '%' and  k.kqbh like '%' || #{kqbh} || '%' and k.nd=#{nd} \n" +
            "\tGROUP BY x.dm\n" +
            "\tORDER BY x.dm")
    List<String> getXzqhKqbh(@Param("fistXzq") String fistXzq,@Param("kqbh")String kqbh,@Param("nd") int nd);


    /**
     * 根据矿区名称条件搜索行政区代码
     * @param fistXzq 用户身份行政区
     * @param kqmc 矿区名称
     * @return
     */
    @Select("select x.dm from kcl_ks k \n" +
            "  left join kcl_cb08_xzqh x   on k.xzqdm =x.dm\n" +
            "\tleft join kcl_cb08_xzqh shi on x.fdm=shi.dm \n" +
            "\tleft JOIN kcl_cb08_xzqh sheng on shi.fdm=sheng.dm \n" +
            "\twhere x.dm::varchar like #{fistXzq} || '%' and  k.ksmc like '%' || #{kqmc} || '%' and k.nd=#{nd}  \n" +
            "\tGROUP BY x.dm\n" +
            "\tORDER BY x.dm")
    List<String> getXzqhKqmc(@Param("fistXzq") String fistXzq,@Param("kqmc")String kqmc,@Param("nd") int nd);

    /**
     * 根据矿山编号条件搜索行政区代码
     * @param fistXzq 用户身份行政区
     * @param ksbh 矿山编号
     * @return
     */
    @Select("select x.dm from kcl_ks k \n" +
            "  left join kcl_cb08_xzqh x   on k.xzqdm =x.dm\n" +
            "\tleft join kcl_cb08_xzqh shi on x.fdm=shi.dm \n" +
            "\tleft JOIN kcl_cb08_xzqh sheng on shi.fdm=sheng.dm \n" +
            "\twhere x.dm::varchar like #{fistXzq} || '%' and  k.ksbh like '%' || #{ksbh} || '%' and k.nd=#{nd} \n" +
            "\tGROUP BY x.dm\n" +
            "\tORDER BY x.dm")
    List<String> getXzqhKsbh(@Param("fistXzq") String fistXzq,@Param("ksbh")String ksbh,@Param("nd")Integer nd);

    /**
     * 根据矿业权人条件搜索行政区代码
     * @param fistXzq 用户身份行政区
     * @param kyqr 矿业权人
     * @return
     */
    @Select("select x.dm from kcl_ks k \n" +
            "  left join kcl_cb08_xzqh x   on k.xzqdm =x.dm\n" +
            "\tleft join kcl_cb08_xzqh shi on x.fdm=shi.dm \n" +
            "\tleft JOIN kcl_cb08_xzqh sheng on shi.fdm=sheng.dm \n" +
            "\twhere x.dm::varchar like #{fistXzq} || '%' and  k.kyqr like '%' || #{kyqr} || '%' and k.nd=#{nd} \n" +
            "\tGROUP BY x.dm\n" +
            "\tORDER BY x.dm")
    List<String> getXzqhKyqr(@Param("fistXzq") String fistXzq,@Param("kyqr")String kyqr,@Param("nd")Integer nd);

    /**
     * 根据许可证号条件搜索行政区代码
     * @param fistXzq 用户身份行政区
     * @param xkzh 许可证号
     * @return
     */
    @Select("select x.dm from kcl_ks k \n" +
            "  left join kcl_cb08_xzqh x   on k.xzqdm =x.dm\n" +
            "\tleft join kcl_cb08_xzqh shi on x.fdm=shi.dm \n" +
            "\tleft JOIN kcl_cb08_xzqh sheng on shi.fdm=sheng.dm \n" +
            "\twhere x.dm::varchar like #{fistXzq} || '%' and  k.xkzh like '%' || #{xkzh} || '%' and k.nd=#{nd} \n" +
            "\tGROUP BY x.dm\n" +
            "\tORDER BY x.dm")
    List<String> getXzqhXkzh(@Param("fistXzq") String fistXzq,@Param("xkzh")String xkzh,@Param("nd")Integer nd);

    /**
     * 查询各个市行政区的dm
     * @param sql
     * @return
     */
    @Select("select fdm from kcl_cb08_xzqh  where fdm<>0 and  dm::VARCHAR in ${sql} group by fdm")
    List<String> getXzqhfdm(String sql);


    /**
     * 根据省行政区代码获取市行政区代码
     * @param xzqh
     * @return
     */
    @Select("(SELECT sheng.dm, sheng.mc\n" +
            " FROM kcl_cb08_xzqh sheng\n" +
            " WHERE sheng.dm = #{xzqh})\n" +
            "UNION ALL\n" +
            "(SELECT shi.dm, shi.mc\n" +
            " FROM kcl_cb08_xzqh shi\n" +
            " JOIN kcl_cb08_xzqh sheng ON sheng.dm = shi.fdm\n" +
            " WHERE sheng.dm =  #{xzqh}\n" +
            " ORDER BY dm)\n" +
            " ")
    List<Map> getShengXzqhList(int xzqh);

    /**
     * 根据市行政区代码获取县行政区代码
     * @param xzqh
     * @return
     */
    @Select(" SELECT xian.dm, xian.mc\n" +
            " FROM kcl_cb08_xzqh xian\n" +
            " JOIN kcl_cb08_xzqh shi ON shi.dm = xian.fdm\n" +
            " WHERE shi.dm = #{xzqh}\n" +
            " ORDER BY dm")
    List<Map> getShiXzqhList(Integer xzqh);
    /**
     * 根据县行政区代码获取县行政区详情
     * @param xzqh
     * @return
     */
    @Select(" SELECT xian.dm, xian.mc\n" +
            " FROM kcl_cb08_xzqh xian\n" +
            " where xian.dm= #{xzqh}")
    List<Map> getXianXzqhList(Integer xzqh);
}
